<?php
namespace app\api\model;
use app\api\model\TaskModel;
use think\Db;

class VideoModel extends \app\common\model\CommonModel
{
    /**
     * @param int    $page          页码
     * @param int    $pagesize      分页数量
     * @param string $status        前测状态
     * @param string $tag           前测标签
     * @param string $userid        上传人
     * @param int    $brand_id      品牌id
     * @param int    $product_id    产品id
     * @param string $name          视频名
     */
    public function getList($userid, $departmentId, $params)
    {
        try {
            $page     = isset($params['page']) && is_numeric($params['page']) ? (int)$params['page'] : 1;
            $pagesize = isset($params['pagesize']) && is_numeric($params['pagesize']) ? (int)$params['pagesize'] : 10;
            $start = ($page-1) * $pagesize;
            $field = "a.*,b.brand,b.team_video,b.team_operate,c.username,d.product_name";
            $sql = "SELECT {$field} FROM video a 
                    LEFT JOIN brand b ON a.brand_id = b.id 
                    LEFT JOIN enum_user c ON a.upload_userid = c.userid 
                    LEFT JOIN product d ON a.product_id = d.id WHERE (FIND_IN_SET($departmentId, b.team_video) > 0 OR FIND_IN_SET($departmentId, b.team_operate) > 0) ";
            // tid
            if(!empty($params['tid'])){
                $sql .= "AND a.tid = '{$params['tid']}' ";
            }
            // 前测状态
            if(!empty($params['status'])){
                $sql .= "AND a.status = '{$params['status']}' ";
            }
            // 标签
            if(!empty($params['tag'])){
                $tag_arr = explode(',',$params['tag']);
                $or = '';
                foreach ($tag_arr as $key => $value) {
                    if(isset($tag_arr[$key+1])){
                        $or .= "tags REGEXP '(,|^|、){$value}(,|$|、)' AND ";
                    }else{
                        $or .= "tags REGEXP '(,|^|、){$value}(,|$|、)' ";
                    }
                }
                $sql .= "AND ($or) ";
            }
            // 用户名(上传人)
            if(!empty($params['username'])){
                $sql .= "AND c.username = '{$params['username']}' ";
            }
            // 编导userid
            if(!empty($params['direct_userid'])){
                $sql .= "AND a.direct_userid = '{$params['direct_userid']}' ";
            }
            // 摄像师userid
            if(!empty($params['camera_userid'])){
                $sql .= "AND a.camera_userid = '{$params['camera_userid']}' ";
            }
            // 平台标签
            if(!empty($params['plats_str'])){
                $plats_str = explode(',',$params['plats_str']);
                $or = '';
                foreach ($plats_str as $k => $v) {
                    if(isset($plats_str[$k+1])){
                        $or .= "FIND_IN_SET('{$v}',plats_str) OR ";
                    }else{
                        $or .= "FIND_IN_SET('{$v}',plats_str)";
                    }
                }
                $sql .= "AND ($or) ";
            }
            // 品牌名
            if(!empty($params['brand'])){
                $sql .= "AND b.brand = '{$params['brand']}' ";
            }
            // 产品名
            if(!empty($params['product'])){
                $sql .= "AND d.product_name = '{$params['product']}' ";
            }
            // 视频名
            if(!empty($params['exact'])){
                $sql .= "AND a.name LIKE '%{$params['exact']}%' ";
            }
            // 是否推送过 1推送过 2未推送
            if(!empty($params['push']) && $params['push'] == 1){
                $sql .= "AND a.push_status = 'PUSHED' ";
            }
            if(!empty($params['push']) && $params['push'] == 2){
                $sql .= "AND a.push_status = 'WATING' ";
            }
            // 时间
            if(!empty($params['start']) && !empty($params['end'])){
                $sql .= "AND a.create_time BETWEEN '{$params['start']}' AND '{$params['end']}' ";
            }
            // 视频类型
            if(!empty($params['type'])){
                $sql .= "AND a.type = '{$params['type']}' ";    
            }
            $order = "ORDER BY a.create_time DESC LIMIT {$start},{$pagesize}";
            $csql = str_replace($field,'count(*)',$sql);
            $sql .= $order;
            $res = $this->getRes($sql);
            if(!empty($res)){
                $res = array_map(function($v){
                    $v['push_count'] = Db::table('push_log')->where('relate_id',$v['id'])->count();
                    $v['direct_username'] = !empty($v['direct_userid']) ? Db::table('enum_user')->where('userid',$v['direct_userid'])->value('username') : '';
                    $v['camera_username'] = !empty($v['camera_userid']) ? Db::table('enum_user')->where('userid',$v['camera_userid'])->value('username') : '';
                    return $v;
                },$res);
            }
            $count = $this->getCount($csql);
            return $this->pageData($res,$pagesize,$page,$count);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }
    
    /**
     * 上传视频
     * @param   string      $userid         用户userid
     * @param   int         $departmentId   部门id
     * @param   int         $params['brand_id']     品牌id
     * @param   int         $params['product_id']   产品id
     * @param   array       $params['video']        视频信息
     * @return  boolean        
     */
    public function upload($userid, $departmentId, $params)
    {
        try {
            //品牌信息
            $brandinfo = Db::name('brand')->where('id', $params['brand_id'])->find();
            if(!$brandinfo){
                throw new \Exception('品牌信息不存在!');
            }
            //产品信息
            $productinfo = Db::table('product')->where('id',$params['product_id'])->find();
            if(!$productinfo){
                throw new \Exception('产品信息不存在!');
            }
            if($productinfo['brand_id'] != $params['brand_id']){
                throw new \Exception('产品信息与品牌信息不一致!');
            }
            //不是自己创建的品牌也不在品牌的协同视频团队,没有上传视频的权限
            if($brandinfo['create_userid'] != $userid && !in_array($departmentId, explode(',',$brandinfo['team_video']))){
                throw new \Exception('您没有权限上传视频至该品牌!');
            }
            
            // 重构,循环插入并请求python
            Db::startTrans();
            $url = "https://fc.souxiu.cn/video_pretest/get_tags";
            foreach ($params['video'] as $key => $value) {
                $video = [];
                $video['tid']                 = !empty($params['tid']) ? $params['tid'] : '';
                $video['type']                = !empty($params['type']) ? $params['type'] : '';
                $video['brand_id']            = $params['brand_id'];
                $video['product_id']          = $params['product_id'];
                $video['name']                = $value['name'];
                $video['video_id']            = '';
                $video['tos_url']             = $value['url'];
                $video['poster']              = $value['poster'];
                $video['tags']                = '';
                $video['status']              = 'NO_PRETEST';
                $video['check_status']        = 'AUDITING';
                $video['pretest_status']      = 'PENDING';
                $video['upload_userid']       = $userid;
                $video['direct_userid']       = !empty($params['direct_userid']) ? $params['direct_userid'] : '';
                $video['camera_userid']       = !empty($params['camera_userid']) ? $params['camera_userid'] : '';
                $video['plats_str']           = !empty($params['plats_str']) ? $params['plats_str'] : 'oceanengine,qianchuan,unidesk,tencent,kuaishou';
                $video['push_userid']         = '';
                $video['push_status']         = 'WATING';
                $video['check_risk']          = '';
                $video['material_efficiency'] = '';
                $video['material_quality']    = '';
                $video['material_creative']   = '';
                $video['upload_error']        = '';
                $video['check_error']         = '';
                $video['pretest_error']       = '';
                $id = Db::table('video')->insertGetId($video);
                
                // 需求内部上传写入操作日志 
                if (!empty($params['tid']) && !empty($params['type'])) {
                    $taskModel  = new TaskModel;
                    $taskinfo = $taskModel->get_task_info_by_tid($params['tid']);
                    if (empty($taskinfo)) {
                        throw new \Exception(ApiErrMsg::TASK_NOT_EXIST);
                    }
                    if ($taskinfo['status'] !== 2) {    // 非制作中状态不可上传
                        throw new \Exception(ApiErrMsg::STATUS_FORBIDDEN_UPLOAD);
                    }
                    // 更细最新时间
                    // 写入上传日志
                    $taskModel->update_task_info($params['tid'],['latest_update' => date('Y-m-d H:i:s')]);
                    $this->write_task_log($params['tid'], $userid, "上传视频：【".$value['name']."】");
                    // 如果是最后一个视频,发送完成待审核邮件给需求发起人
                    $tasklist = $taskModel->get_task_list($params['tid'],'type,total');
                    $total = array_reduce($tasklist, function($carry, $item) {
                        return $carry + (isset($item['total']) ? $item['total'] : 0);
                    }, 0);
                    $uploaded = $taskModel->get_task_uploaded_by_tid($params['tid']);
                    if ($uploaded == $total) {
                        $emailObj   = new \app\common\tool\Email;
                        $info = [
                            'to_email'          => $taskinfo['create_email'],
                            'to_username'       => $taskinfo['create_username'],
                            'task_name'         => $taskinfo['name'],
                            'charge_username'   => $taskinfo['charge_username'],
                            'submit_time'       => date('Y-m-d H:i:s'),
                        ];
                        $emailObj->handle_send('TASK_IS_UNDER_REVIEW', $info);
                        $taskModel->update_task_info($params['tid'],['status' => 4]);    
                    }
                }

                $post_field = [
                    'id'            => $id,
                    'video_url'     => $value['url'],
                    'file_name'     => $value['name'],
                    'advertiser_id' => $productinfo['advertiser_id'],
                    'plat'          => $productinfo['plat'],
                ];
                $curl = curl_init();
                curl_setopt_array($curl, [
                CURLOPT_URL => $url,
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_ENCODING => "",
                CURLOPT_MAXREDIRS => 10,
                CURLOPT_TIMEOUT => 30,
                CURLOPT_SSL_VERIFYPEER => false,
                CURLOPT_SSL_VERIFYHOST => false,
                CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                CURLOPT_CUSTOMREQUEST => "POST",
                CURLOPT_POSTFIELDS => json_encode($post_field),
                CURLOPT_HTTPHEADER => [
                    "content-type: application/json"
                ],
                ]);
                $response = curl_exec($curl);
                $err = curl_error($curl);
                curl_close($curl);
                if ($err) {
                    Db::rollback();
                    throw new \Exception('请求python接口失败!'.'错误信息:'.$err);
                }
                $ret = json_decode($response,true); // 获取python接口返回值
                if ($ret['code'] != 1) {
                    Db::rollback();
                    throw new \Exception('请求python接口失败!'.'错误信息:'.$ret['msg']);
                }
            }
            Db::commit();
            return true;
        } catch (\Exception $e) {
            Db::rollback();
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 获取推送日志
     * @param string $userid
     * @param string $departmentId
     * @param array  $params
     */
    public function getLog($userid, $departmentId , $params)
    {
        try {
            $page     = isset($params['page']) && is_numeric($params['page']) ? (int)$params['page'] : 1;
            $pagesize = isset($params['pagesize']) && is_numeric($params['pagesize']) ? (int)$params['pagesize'] : 10;
            $start = ($page-1) * $pagesize;
            $sql = "SELECT * FROM push_log WHERE relate_id = {$params['id']} ";
            $order = "ORDER BY create_time DESC LIMIT {$start},{$pagesize}";
            $csql = str_replace('*','count(*)',$sql);
            $sql .= $order;
            $res = $this->getRes($sql);
            $count = $this->getCount($csql);
            return $this->pageData($res,$pagesize,$page,$count);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 视频重新检测
     * @param int $id
     * @param string $plat
     */
    public function check_again($id)
    {
        try {
            //视频信息
            $res = Db::name('video')
                    ->field('a.id,a.video_id,a.name,a.product_id,a.tos_url,a.status,a.push_userid,a.push_status,a.create_time,b.product_name,b.advertiser_id,b.plat')
                    ->alias('a')
                    ->join('product b','a.product_id = b.id')
                    ->where("a.id = {$id}")
                    ->find();
            if(empty($res)){
                throw new \Exception('视频不存在!');
            }
            // 状态限制,前测等待和前测中的不可重新检测
            if($res['status'] == 'NO_PRETEST' || $res['status'] == 'IN_PRETEST'){
                throw new \Exception('前测等待和前测中的不可重新检测!');    
            }
            // 1.请求python接口
            $url = "https://fc.souxiu.cn/video_pretest/again";
            $post_field = [
                'id'            => $res['id'],
                'video_url'     => $res['tos_url'],
                'video_id'      => $res['video_id'],
                'file_name'     => $res['name'],
                'advertiser_id' => $res['advertiser_id'],
                'plat'          => $res['plat'],
            ];
            $curl = curl_init();
            curl_setopt_array($curl, [
            CURLOPT_URL => $url,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_ENCODING => "",
            CURLOPT_MAXREDIRS => 10,
            CURLOPT_TIMEOUT => 30,
            CURLOPT_SSL_VERIFYPEER => false,
            CURLOPT_SSL_VERIFYHOST => false,
            CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            CURLOPT_CUSTOMREQUEST => "POST",
            CURLOPT_POSTFIELDS => json_encode($post_field),
            CURLOPT_HTTPHEADER => [
                "content-type: application/json"
            ],
            ]);
            $response = curl_exec($curl);
            $err = curl_error($curl);
            curl_close($curl);
            if ($err) {
                throw new \Exception('请求python接口失败!'.'错误信息:'.$err);
            }
            $ret = json_decode($response,true); // 获取python接口返回值
            if ($ret['code'] != 1) {
                throw new \Exception('请求python接口失败!'.'错误信息:'.$ret['msg']);
            }
            // 2.清空历史检测结果
            $res = Db::name('video')->where('id',$id)->update([
                'status'                => 'NO_PRETEST',
                'check_status'          => 'AUDITING',
                'pretest_status'        => 'WATING',
                'tags'                  => '',
                'check_risk'            => '',
                'material_efficiency'   => '',
                'material_quality'      => '',
                'material_creative'     => '',
            ]);
            return true;
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 视频推送
     * @param string $userid
     * @param string $departmentId
     * @param array $advertiser_ids
     * @param array $video_ids
     * @return bool
     * @throws \Exception
     */
    public function push($userid, $departmentId, $params)
    {
        try{
            //1.巨量引擎 oceanengine 2.千川:qianchuan 3.Unidesk:unidesk 
            $plat = !empty($params['plat']) ? $params['plat'] : 'oceanengine';
            $video_info = $params['video_info'];
            $advertiser_info = $params['advertiser_info'];
            $video_ids = implode(',',array_column($video_info,'id'));;
            $video_res = Db::name('video')->where('id','in',$video_ids)->order('id','desc')->select();
            if(empty($video_res) || count($video_info) != count($video_res)){
                throw new \Exception('视频不存在!');
            }
            $ids_arr = [];
            $video_id_arr = [];
            $advertiser_id_arr = [];
            $push_log = [];
            foreach ($video_res as $key => $value) {
                if(empty($value['video_id'])){
                    throw new \Exception('所选视频id不能为空!');
                }
                $ids_arr[] = $value['id'];
                $video_id_arr[] = $value['video_id'];
                foreach ($advertiser_info as $k => $v) {
                    $push_log[$key.'_'.$k]['relate_id'] = $value['id'];    
                    $push_log[$key.'_'.$k]['video_id'] = $value['video_id'];    
                    $push_log[$key.'_'.$k]['advertiser_name'] = $v['advertiser_name'];    
                    $push_log[$key.'_'.$k]['advertiser_id'] = $v['advertiser_id'];    
                    $push_log[$key.'_'.$k]['push_time'] = date('Y-m-d H:i:s');    
                    $push_log[$key.'_'.$k]['status'] = 'WATING';
                    $push_log[$key.'_'.$k]['push_error'] = '';    
                    $push_log[$key.'_'.$k]['plat'] = $plat;    
                }   
            }
            $advertiser_id_arr = array_column($advertiser_info,'advertiser_id');
            $push_log = array_values($push_log);
            $url = "https://fc.souxiu.cn/video_pretest/push";
            $post_field = [
                'ids'               => $ids_arr,
                'video_ids'         => $video_id_arr,
                'advertiser_ids'    => $advertiser_id_arr,
                'plat'              => $plat,
            ];
            $curl = curl_init();
            curl_setopt_array($curl, [
            CURLOPT_URL => $url,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_ENCODING => "",
            CURLOPT_MAXREDIRS => 10,
            CURLOPT_TIMEOUT => 30,
            CURLOPT_SSL_VERIFYPEER => false,
            CURLOPT_SSL_VERIFYHOST => false,
            CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            CURLOPT_CUSTOMREQUEST => "POST",
            CURLOPT_POSTFIELDS => json_encode($post_field),
            CURLOPT_HTTPHEADER => [
                "content-type: application/json"
            ],
            ]);
            $response = curl_exec($curl);
            $err = curl_error($curl);
            curl_close($curl);
            if ($err) {
                throw new \Exception('请求python接口失败!'.'错误信息:'.$err);
            }
            $ret = json_decode($response,true); // 获取python接口返回值
            if ($ret['code'] != 1) {
                throw new \Exception('请求python接口失败!'.'错误信息:'.$ret['msg']);
            }
            Db::table('push_log')->insertAll($push_log);
            Db::name('video')->where('id','in',$video_ids)->update(['push_status' => 'PUSHED']);
            return true;
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    public function getHistory($userid, $departmentId, $params)
    {
        try {
            $plat = !empty($params['plat']) ? $params['plat'] : 'oceanengine';
            $page     = isset($params['page']) && is_numeric($params['page']) ? (int)$params['page'] : 1;
            $pagesize = isset($params['pagesize']) && is_numeric($params['pagesize']) ? (int)$params['pagesize'] : 10;
            $start = ($page-1) * $pagesize;
            $field = "DISTINCT(advertiser_id),advertiser_name,plat";
            $sql = "SELECT {$field} FROM push_log a 
                    LEFT JOIN 
                    (SELECT a.id FROM video a LEFT JOIN brand b ON a.brand_id = b.id WHERE 
                    (FIND_IN_SET($departmentId, b.team_video) > 0 OR FIND_IN_SET($departmentId, b.team_operate) > 0)) b 
                    ON a.relate_id = b.id WHERE a.plat = '{$plat}' ";
            $res = Db::query($sql);
            $order = "ORDER BY a.advertiser_name DESC LIMIT {$start},{$pagesize}";
            $csql = str_replace($field,"count(DISTINCT advertiser_id) as 'count(*)'",$sql);
            $sql .= $order;
            $res = $this->getRes($sql);
            $count = $this->getCount($csql);
            return $this->pageData($res,$pagesize,$page,$count);
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    /**
     * 删除视频
     * @param int $id
     * @return bool
     * @throws \Exception
     */
    public function delete_video($userid, $params)
    {
        try {
            $id = $params['id'];
            $res = Db::table('video')->where('id',$id)->find();
            if(empty($res)){
                return false;
            }
            if($res['status'] == 'NO_PRETEST' || $res['status'] == 'IN_PRETEST'){
                throw new \Exception('该视频正在前测中，无法删除!');
            }
            if($res['push_status'] == 'PUSHED'){
                throw new \Exception('该视频已推送至广告主，无法删除!');
            }
            if (!empty($res['tid'])) {
                $this->write_task_log($res['tid'], $userid, "删除视频：【".$res['name']."】");    
            }
            $ret = Db::table('video')->where('id',$id)->delete();
            if($ret !== false){
                return true;
            }
            return false;   
        } catch (\Exception $e) {
            throw new \Exception($e->getMessage());
        }
    }

    public function get_task_video($tid)
    {
        return Db::table('video')->where('tid',$tid)->select();
    }
}